package com.devunion.bogdan.model.project.dao;

import com.devunion.bogdan.model.project.*;
import com.devunion.bogdan.model.project.info.ProjectInfo;
import com.devunion.bogdan.persistence.BaseDaoObject;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.*;


/**
 * <p>Hibernate implementation of <code>UserDao</code></p>
 *
 * @author Viktar Liaskovich
 */
public class HibernateProjectDao extends BaseDaoObject<Project> implements ProjectDao {

    /**
     * {@inheritDoc}
     */
    public Project getByKey(Serializable key) {
        return (Project) hibernateTemplate.get(Project.class, key);
    }

    /**
     * {@inheritDoc}
     */
    public List<Project> loadAll() {
        return hibernateTemplate.loadAll(Project.class);
    }

    public List<ProjectInfo> loadProjectInfos() {
        return (List<ProjectInfo>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List list = session.createQuery("select projectId, name from Project")
                        .list();

                List<ProjectInfo> result = new ArrayList<ProjectInfo>();
                if (list != null) {
                    for (Object p : list) {
                        ProjectInfo projectInfo = new ProjectInfo();

                        projectInfo.setProjectId((Long) ((Object[]) p)[0]);
                        projectInfo.setName((String) ((Object[]) p)[1]);

                        result.add(projectInfo);
                    }

                    return result;
                }

                return Collections.emptyList();
            }
        });
    }

    public List<Page> loadPages(final Long projectId, final Long paperId, final String annotationType, final String annotationText) {
        return (List<Page>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                StringBuilder builder = new StringBuilder("select pages from Project p left outer join p.entries as entries left outer join entries.pages as pages" +
                        " left outer join pages.annotations as annotations where " +
                        "p.projectId = :pId and entries.id = :paperId");
                Map<String, Object> parameters = buildQuery(builder, null, null, null, null, null,
                        null, null, null, null, null, null, null, null, null,
                        annotationType, annotationText);
                builder.append(" order by pages.pageNumber");
                Query q = session.createQuery(builder.toString());

                for (Map.Entry<String, Object> entry : parameters.entrySet()) {
                    q.setParameter(entry.getKey(), entry.getValue());
                }
                q.setParameter("pId", projectId, Hibernate.LONG);
                q.setParameter("paperId", paperId, Hibernate.LONG);

                return new ArrayList<Page>(q.list());
            }
        });
    }

    public Set<Project> getProjectByCriteria(final String firstName, final String lastName, final String projectName, final String title,
                                             final String type, final String language, final Number pagesMin, final Number pagesMax,
                                             final Date creationMin, final Date creationMax, final Date exactMin, final Date exactMax,
                                             final Date endMin, final Date endMax, final String annotationType, final String annotationText) {
        return (Set<Project>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                StringBuilder builder = new StringBuilder("select p from Project p inner join p.entries as entries" +
                        " inner join entries.pages as pages inner join pages.annotations as annotations where p.name is not null");
                Map<String, Object> parameters = buildQuery(builder, firstName, lastName, projectName, title, type,
                        language, pagesMin, pagesMax, creationMin, creationMax, exactMin, exactMax, endMin, endMax,
                        annotationType, annotationText);
                Query q = session.createQuery(builder.toString());
                for (Map.Entry<String, Object> entry : parameters.entrySet()) {
                    q.setParameter(entry.getKey(), entry.getValue());
                }
                return new HashSet<Project>(q.list());
            }
        });
    }

    public Set<Paper> getProjectPapersByCriteria(final Long id, final String firstName, final String lastName, final String title, final String type,
                                                 final String language, final Number pagesMin, final Number pagesMax,
                                                 final Date creationMin, final Date creationMax, final Date exactMin, final Date exactMax,
                                                 final Date endMin, final Date endMax, final String annotationType, final String annotationText) {
        return (Set<Paper>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                StringBuilder builder = new StringBuilder("select entries from Project p inner join p.entries as entries" +
                        " inner join entries.pages as pages inner join pages.annotations as annotations where p.id = :pId");
                Map<String, Object> parameters = buildQuery(builder, firstName, lastName, null, title, type,
                        language, pagesMin, pagesMax, creationMin, creationMax, exactMin, exactMax, endMin, endMax,
                        annotationType, annotationText);
                Query q = session.createQuery(builder.toString());
                for (Map.Entry<String, Object> entry : parameters.entrySet()) {
                    q.setParameter(entry.getKey(), entry.getValue());
                }
                q.setParameter("pId", id);
                return new HashSet<Project>(q.list());
            }
        });
    }

    private Map<String, Object> buildQuery(StringBuilder builder, final String firstName, final String lastName, final String projectName, final String title,
                                           final String type, final String language, final Number pagesMin, final Number pagesMax,
                                           final Date creationMin, final Date creationMax, final Date exactMin, final Date exactMax,
                                           final Date endMin, final Date endMax, final String annotationType, final String annotationText) {
        Map<String, Object> parameters = new HashMap<String, Object>();

        if (!StringUtils.isBlank(firstName)) {
            builder.append(" and lower(entries.info.author.firstName) like :firstName");
            parameters.put("firstName", "%" + firstName.toLowerCase() + "%");
        }

        if (!StringUtils.isBlank(lastName)) {
            builder.append(" and lower(entries.info.author.lastName) like :lastName");
            parameters.put("lastName", "%" + lastName.toLowerCase() + "%");
        }

        if (!StringUtils.isBlank(projectName)) {
            builder.append(" and lower(p.name) like :name");
            parameters.put("name", "%" + projectName.toLowerCase() + "%");
        }

        if (!StringUtils.isBlank(title)) {
            builder.append(" and lower(entries.info.title) like :title");
            parameters.put("title", "%" + title.toLowerCase() + "%");
        }

        if (!StringUtils.isBlank(type)) {
            builder.append(" and entries.info.type = :type");
            parameters.put("type", PaperType.valueOf(type));
        }

        if (!StringUtils.isBlank(language)) {
            builder.append(" and lower(entries.info.language) like :language");
            parameters.put("language", "%" + language.toLowerCase() + "%");
        }

        if (pagesMin != null) {
            builder.append(" and entries.info.numberOfPages >= :pagesMin");
            parameters.put("pagesMin", pagesMin);
        }

        if (pagesMax != null) {
            builder.append(" and entries.info.numberOfPages <= :pagesMax");
            parameters.put("pagesMax", pagesMax);
        }

        if (creationMin != null) {
            builder.append(" and entries.info.creationStartDate >= :creationMin");
            parameters.put("creationMin", creationMin);
        }

        if (creationMax != null) {
            builder.append(" and entries.info.creationStartDate <= :creationMax");
            parameters.put("creationMax", creationMax);
        }

        if (exactMin != null) {
            builder.append(" and entries.info.creationExactDate >= :exactMin");
            parameters.put("exactMin", exactMin);
        }

        if (exactMax != null) {
            builder.append(" and entries.info.creationExactDate <= :exactMax");
            parameters.put("exactMax", exactMax);
        }

        if (endMin != null) {
            builder.append(" and entries.info.creationEndDate >= :endMin");
            parameters.put("endMin", endMin);
        }

        if (endMax != null) {
            builder.append(" and entries.info.creationEndDate <= :endMax");
            parameters.put("endMax", endMax);
        }

        if (!StringUtils.isBlank(annotationType)) {
            builder.append(" and annotations.type = :annotationType");
            parameters.put("annotationType", AnnotationType.valueOf(annotationType));
        }

        if (!StringUtils.isBlank(annotationText)) {
            builder.append(" and lower(annotations.text) like :annotationText");
            parameters.put("annotationText", "%" + annotationText.toLowerCase() + "%");
        }

        return parameters;
    }
}
